package com.augurit.gzcgwsp.ljzyz.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.augurit.common.base.dao.BaseDao;
import com.augurit.gis.common.util.DateUtil;
import com.augurit.gzcgwsp.commonex.util.lang.DateUtilsEX;
import com.augurit.gzcgwsp.jzfqwczz.common.entity.YwYnBusinfoHfqk;
import com.augurit.gzcgwsp.ljzyz.entity.YwYnBusinfo;

@Repository
public class YwYnBusinfoDao extends BaseDao<YwYnBusinfo, Long> {
	
	/**
	 * 根据案件id查找车辆列表
	 * @param ajid
	 * @return
	 */
	public List<YwYnBusinfo> findPageByAjid(String qyid,String pageNo){
		List<YwYnBusinfo> list=null;
		String hql="from YwYnBusinfo businfo where businfo.qyid='"+Long.parseLong(qyid)+"' order by businfo.clbh desc";
		Query query = this.getSession().createQuery(hql);
		int aa = (Integer.parseInt(pageNo)-1)*10;
//		System.out.println("aa = "+aa);
		int totalCount = query.list().size();
		int pageCount = (totalCount+10-1)/10;
//		System.out.println("totalCount = "+totalCount);
//		System.out.println("pageCount = "+pageCount);
		query.setFirstResult(aa);
		query.setMaxResults(10);
		list=query.list();
		//list=this.getSession().createQuery(hql).list();
		if(list.size()>0){
			for(int i=0;i<list.size();i++){
				Date ccrq = list.get(i).getCcrq();
				Date djrq = list.get(i).getDjrq();
				String zyzyxrq=getZyzyxqByBusId(list.get(i).getId());
				list.get(i).setZyzyxqStr(zyzyxrq);
				Date zyzyxq = list.get(i).getZyzyxq();
				if(ccrq != null){
					list.get(i).setCcrqStr(DateUtil.date2ChinaString(ccrq,3));
				}
				
				if(djrq != null){
					list.get(i).setDjrqStr(DateUtil.date2ChinaString(djrq,3));
				}
				
				if(zyzyxq != null){
					list.get(i).setZyzyxqStr(DateUtil.date2ChinaString(zyzyxq,3));
				}
				list.get(i).setPageCount((long)pageCount);
				list.get(i).setTotalCount((long)totalCount);
				
				
				

			}
			
		}
		return list;
	}
	
	
	public String getZyzyxqByBusId(Long busId) {
		String sql=" select hfqk from YwYnBusinfoHfqk hfqk,YwYnBusinfoNssy " +
				"nssy where hfqk.nssyId=nssy.id and nssy.busId="+busId+" and nssy.sts='0' and hfqk.sts='0' and hfqk.yxjsrq  is not null order by hfqk.yxjsrq desc ";
		Query query=this.getSession().createQuery(sql);
		List<YwYnBusinfoHfqk> list=query.list();
		if(list.size()>0){
			Date ksrq=list.get(0).getYxksrq();
			Date jsrq=list.get(0).getYxjsrq();
			return DateUtilsEX.dateFormate(ksrq, "yyyy-MM-dd")+
			"~"+DateUtilsEX.dateFormate(jsrq, "yyyy-MM-dd");
		}
		return "";
	}
	
	/**
	 * 根据企业id查找车辆列表
	 * @param ajid
	 * @return
	 */
	public List<YwYnBusinfo> findPageByQyid(String qyid){
		List<YwYnBusinfo> list=null;
		String hql="from YwYnBusinfo businfo where businfo.qyid="+qyid+" order by businfo.id desc";
		Query query = this.getSession().createQuery(hql);
		list=query.list();
		//list=this.getSession().createQuery(hql).list();
		if(list.size()>0){
			for(int i=0;i<list.size();i++){
				Date ccrq = list.get(i).getCcrq();
				Date djrq = list.get(i).getDjrq();
				Date zyzyxq = list.get(i).getZyzyxq();
				Date xszyxrq = list.get(i).getXszyxrq();
				if(ccrq != null){
					list.get(i).setCcrqStr(DateUtil.date2ChinaString(ccrq,3));
				}else{
					list.get(i).setCcrqStr("");
				}
				
				if(djrq != null){
					list.get(i).setDjrqStr(DateUtil.date2ChinaString(djrq,3));
				}else{
					list.get(i).setDjrqStr("");
				}
				
				if(zyzyxq != null){
					list.get(i).setZyzyxqStr(DateUtil.date2ChinaString(zyzyxq,3));
				}else{
					list.get(i).setZyzyxqStr("");
				}
				
				if(xszyxrq != null){
					list.get(i).setXszyxrqStr(DateUtil.date2ChinaString(xszyxrq,3));
				}else{
					list.get(i).setXszyxrqStr("");
				}
			}			
		}
		return list;
	}
	
	
	/**
	 * 根据主键查找车辆信息
	 * @param id
	 * @return
	 */
	public YwYnBusinfo findById(String id){
		List<YwYnBusinfo> list=null;
		YwYnBusinfo businfo=null;
		String hql="from YwYnBusinfo businfo where businfo.id="+id;
		list=this.getSession().createQuery(hql).list();
		if(list!=null && list.size()>0){
			businfo=list.get(0);
		}
		return businfo;
	}
	
	
	
	/**
	 * 简单SQL查询  
	 * 
	 * @param sql
	 * @return
	 */
	public  List<Map> queryForList(String sql) {
		Connection conn = null;
		Statement st = null;
		ResultSet rs = null;
		List<Map> tempList = new ArrayList<Map>();
		try {
			long timeBefore = System.currentTimeMillis();
			conn = getConnection();
			st = getStatement(conn);
			rs = st.executeQuery(sql);
			ResultSetMetaData meta = rs.getMetaData();
			int columnCount = meta.getColumnCount();
			while(rs.next()) {
				Map<String, String> map = new LinkedHashMap<String, String>();
				for(int i=1; i<=columnCount; i++) {
					String colName = meta.getColumnLabel(i).toLowerCase();
					String colValue = rs.getString(i);
					map.put(colName, colValue);
				}
				tempList.add(map);
			}
			
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			free(conn, st, rs);
		}
		
		return tempList;
	}
	
	
	public  Connection getConnection() {
		Connection conn = null;
		try {
			conn=getSession().connection();
		} catch (HibernateException e) {
			e.printStackTrace();
		}
		
		return conn;
	}
	
	/**  
	 * 关闭连接资源  
	 * @param conn  
	 */
	public static void free(Connection conn, Statement st, ResultSet rs) {
		if(rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	/*
	 * 获取Statement，并且设置最长的查询时间，超过该时间会自动抛出异常
	 */
	private  Statement getStatement(Connection conn) throws Exception {
		Statement t = conn.createStatement();
		return t;
	}
}